<?php if (!defined('TL_ROOT')) die('You can not access this file directly!');

/**
 * TYPOlight webCMS
 * Copyright (C) 2005-2009 Leo Feyer
 *
 * This program is free software: you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation, either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this program. If not, please visit the Free
 * Software Foundation website at http://www.gnu.org/licenses/.
 *
 * PHP version 5
 * @copyright  Leo Feyer 2005-2009
 * @author     Leo Feyer <leo@typolight.org>
 * @package    Backend
 * @license    LGPL
 * @filesource
 */


/**
 * Class ModuleMaintenance
 *
 * Back end module "maintenance".
 * @copyright  Leo Feyer 2005-2009
 * @author     Leo Feyer <leo@typolight.org>
 * @package    Controller
 */
class ModuleMemberList extends BackendModule
{

	/**
	 * Template
	 * @var string
	 */
	protected $strTemplate = 'be_memberlist';


	/**
	 * Generate module
	 */
	protected function compile()
	{
		$this->loadLanguageFile('be_memberlist');
		
		if (!$this->Input->get('act') || $this->Input->get('act') == 'list')
		{
			if ($this->Input->post('FORM_SUBMIT') != 'save')
			{
				// Generate order list.
				$this->Template->members = $this->generateMemberList();
				$this->Template->limitValue = ($this->Input->post('tl_limit')) ? $this->Input->post('tl_limit') : "0,30";
			}			
			else
			{
				// Validate.
				$errorMsg = "";
				$password = "";
				$strSocial = "";
				$strSocial = implode(',', $this->Input->post('social'));
				$objEmail = $this->Database->prepare("SELECT COUNT(`id`) AS cnt FROM `tl_member` WHERE `email`=? AND `id`!=?")
											->execute($this->Input->post('email'), $this->Input->post('id_member'), time(), time());
				$cnt_email = intval($objEmail->cnt);
				
				if ($cnt_email <= 0)
				{
					if ($this->Input->post('password'))
					{	
						
							$password = trim($this->Input->post('password'));
							$password_2 = substr(md5(uniqid('', true)), 0, 23);
							$password_1 = sha1($password_2 . $password);
							$password = "$password_1:$password_2";			
							
							$objMember = $this->Database->prepare("UPDATE tl_member SET firstname=?, lastname=?, gender=?, social=?, state=?, city=?, email=?, password=?, dateOfBirth=? WHERE id=?")
											->execute($this->Input->post('firstname'), $this->Input->post('lastname'), $this->Input->post('gender'), $strSocial, $this->Input->post('province'), $this->Input->post('city'), $this->Input->post('email'), $password, strtotime($this->Input->post('birthday')), $this->Input->post('id_member'));				
						
					}
					else
					{
						$objMember = $this->Database->prepare("UPDATE tl_member SET firstname=?, lastname=?, gender=?, social=?, state=?, city=?, email=?, dateOfBirth=? WHERE id=?")
										->execute($this->Input->post('firstname'), $this->Input->post('lastname'), $this->Input->post('gender'), $strSocial, $this->Input->post('province'), $this->Input->post('city'), $this->Input->post('email'), strtotime($_POST['birthday']), $this->Input->post('id_member'));
					}
				}
				else
				{
					$error = "Another customer is already using this email. Please choose another.";
				}
				
				$this->redirect("typolight/main.php?do=memberlist&act=detail&id=" . $this->Input->post('id_member') . (($errorMsg) ? ('&error=' . $errorMsg) : ''));
			}
		}
		else
		{
			switch ($this->Input->get('act'))
			{
				case 'detail':
					$this->Template = new BackendTemplate("be_memberdetail");
					$this->Template->members = $this->getMemberDetail($this->Input->get('id'));
					$this->Template->limitValue = ($this->Input->post('tl_limit')) ? $this->Input->post('tl_limit') : "0,30";
					// Get the state, country and city.
					$this->loadLanguageFile('countries');
					include(TL_ROOT . '/system/config/countries.php');
		
					// Get the states.
					$arrStates = array();
					$objStates = $this->Database->prepare("SELECT * FROM `china_province` ORDER BY `province_en`")
												->execute(time());
					while ($objStates->next())
					{
						$arrStates[$objStates->id] = stripslashes($objStates->province_en);
					}
					
					// Get city.
					$arrCitys = array();			
					$objCitys = $this->Database->prepare("SELECT * FROM `china_city` ORDER BY `city_en`")
												->execute(time());
												
					while ($objCitys->next())
					{
						$arrCitys[$objCitys->id] = stripslashes($objCitys->city_en);
					}
					
					$this->Template->arrSates = $arrStates;
					$this->Template->arrCitys = $arrCitys;
					$this->Template->arrCountries = $countries;
					$this->Template->basePath = $this->Environment->base;
					break;
				case 'delete':
					$objRecord = $this->Database->prepare("SELECT * FROM `tl_member` WHERE id=?")
							  ->limit(1)
							  ->execute($this->Input->get('id'));
							  
					if ($objRecord->numRows > 0)
					{
						$email = $objRecord->email;
						
						$this->Database->prepare("DELETE FROM tl_newsletter_recipients WHERE email=?")
								   ->limit(1)
								   ->execute($email);
						
						$this->Database->prepare("DELETE FROM `tl_member` WHERE id=?")
									->limit(1)
									->execute($this->Input->get('id'));
					}
					
					$this->redirect("typolight/main.php?do=memberlist");
					break;
				case 'show':
					break;
				case 'export_memberlist':
					$this->exportMemberList();
					break;
				case 'export_inventory':
					$this->exportInventory();
					break;
				case 'export_wishlist':
					$id_member = $this->Input->get('id_member') ? $this->Input->get('id_member') : 0;
					$this->exportWishlist($id_member);
					break;
				default:
					break;
			}
		}
	}
	
	/**
	 * Handle the html 
	 */
	protected function generateMemberList()
	{
		$strLimit = "LIMIT 0,30";
		$strWhere = "WHERE 1";
		$arrWildcards = array();
		$arrValues = array();
		//$cnt = 0;
		
		if ($this->Input->post('FORM_SUBMIT'))
		{
			if ($this->Input->post('tl_limit'))
				if ($this->Input->post('tl_limit') == 'all')
					$strLimit = "";
				else
					$strLimit = "LIMIT " . $this->Input->post('tl_limit');
			
			if ($this->Input->post('tl_member_search') && $this->Input->post('tl_search_value'))
			{
				switch ($this->Input->post('tl_member_search'))
				{
					case 'username':
						//$strWhere = "WHERE `username` = '" . $this->Input->post('tl_search_value') . "'";
						$arrWildcards[] = "CONCAT(`firstname`, ' ', `lastname`) LIKE ?";
						$arrValues[] = '%' . $this->Input->post('tl_search_value') . '%';
						break;
					case 'email':
						//$strWhere = "WHERE `email` = '" . $this->Input->post('tl_search_value') . "'";
						$arrWildcards[] = "`email` LIKE ?";
						$arrValues[] = '%' . $this->Input->post('tl_search_value') . '%';
						break;
					default;
						break;
				}
			}
			$this->Template->member_search = $this->Input->post('tl_member_search');
			$this->Template->search_value = $this->Input->post('tl_search_value');
		}
		
		$objMembersCnt = $this->Database->prepare("SELECT COUNT(`id`) AS cnt
												FROM `tl_member`
												" . $strWhere . (($arrWildcards) ? " AND " . implode(' AND ', $arrWildcards) : "") . " 
												ORDER BY `tstamp`")
									->execute($arrValues, time(), time());
							
		if ($objMembersCnt->numRows < 1)
			return;
		$this->Template->memberCnt = $objMembersCnt->cnt;
									
		$objMembers = $this->Database->prepare("SELECT `id`, `email`, `firstname`, `lastname`, `tstamp`, `registtime` 
												FROM `tl_member`
												" . $strWhere . (($arrWildcards) ? " AND " . implode(' AND ', $arrWildcards) : "") . "  
												ORDER BY `tstamp`" . $strLimit)
									->execute($arrValues, time(), time());
		
		if ($objMembers->numRows < 1)
			return;
		
		$arrMembers = array();
		
		while ($objMembers->next())
		{
			$arrMembers[] = array(
				'id' => $objMembers->id,
				'User Name' => $objMembers->firstname . ' ' . $objMembers->lastname,
				'Email' => $objMembers->email,
				'Register Date' => date('Y-m-d', $objMembers->registtime),				
			);
		}
		
		return $arrMembers; 
	}
	
	/**
	 * Show order details
	 * @params int
	 * @return array - order detail
	 */
	protected function getMemberDetail($id_member=0)
	{
		if (!$id_member)
			return;
		
		$objMembersCnt = $this->Database->prepare("SELECT COUNT(`id`) AS cnt
												FROM `tl_member`
												ORDER BY `tstamp`")
									->execute(time(), time());
									
		if ($objMembersCnt->numRows < 1)
			return;
			
		$this->Template->memberCnt = $objMembersCnt->cnt;
		
		$objMember = $this->Database->prepare("SELECT * 	  
												FROM `tl_member` 
												WHERE `tl_member`.`id`=?")
						->execute($id_member);
		
		if ($objMember->numRows < 1)
			return;
		
		$arrMember = array();
		while ($objMember->next())
		{				
			$arrMember = array(
				'id_member' => $objMember->id,
				'firstname' => $objMember->firstname,
				'lastname' => $objMember->lastname,
				'email' => $objMember->email,
				'gender' => $objMember->gender,
				'dateOfBirth' => $objMember->dateOfBirth,
				'state' => $objMember->state,
				'city' => $objMember->city,
				'social' => explode(',', $objMember->social),
				'mobile' => $objMember->mobile,
				'cur' => $objMember->cur ? $objMember->cur : 'No currency selected',
			);			
		}
		
		$objAddressbooks = $this->Database->prepare("SELECT `tl_address_book`.`firstname` AS add_firstname, `tl_address_book`.`lastname` AS add_lastname, `tl_address_book`.`street` AS add_street, `tl_address_book`.`street_2` AS add_street_2, `tl_address_book`.`city` AS add_city, `tl_address_book`.`state` AS add_state, `tl_address_book`.`country` AS add_country, `tl_address_book`.`postcode` AS add_postcode, `tl_address_book`.`default_address` 
													FROM `tl_address_book`
													WHERE `tl_address_book`.`uid`=?")
						->execute($id_member);
						
		while ($objAddressbooks->next())
		{
			$arrAddressbook[] = array(
				'add_firstname' => $objAddressbooks->add_firstname,
				'add_lastname' => $objAddressbooks->add_lastname,
				'add_street' => $objAddressbooks->add_street,
				'add_street_2' => $objAddressbooks->add_street_2,
				'add_city' => $objAddressbooks->add_city,
				'add_state' => $objAddressbooks->add_state,
				'add_country' => $objAddressbooks->add_country,
				'add_postcode' => $objAddressbooks->add_postcode,
				'default_address' => $objAddressbooks->default_address
			);
		}
		
		$objOrders = $this->Database->prepare("SELECT `tl_orders`.`orderno`, `tl_orders`.`id` AS id_order, `tl_orders`.`date_add`, (CASE `tl_orders`.`odr_status` WHEN 1 THEN 'Payment accepted' WHEN 2 THEN 'Preparation in progress' WHEN 3 THEN 'Shipping' WHEN 6 THEN 'Cancelled' ELSE 'Delivered' END) AS status, `tl_orders`.`total_paid_real`, `tl_orders`.`payment` 
													FROM `tl_orders`
													WHERE `tl_orders`.`id_member`=?")
						->execute($id_member);
		
		while ($objOrders->next())
		{
			$arrOrders[] = array(
				'id_order' => $objOrders->id_order,
				'orderno' => $objOrders->orderno, 
				'date_add' => $objOrders->date_add,
				'memberName' => $arrMember['firstname'] . ' ' . $arrMember['lastname'],
				'total_paid_real' => $objOrders->total_paid_real,
				'status' => (($objOrders->status== 'Payment accepted' && $objOrders->payment == 'COD') ? 'Preparation in progress' : $objOrders->status),
			);
		}
		
		// wishlist
		$objWishlists = $this->Database->prepare("SELECT pro_cata.`id` AS id_catalog, pro_cata.`category` AS id_category, pro_cata.`skuid`, pro_category.`name` AS 'category', pro_cate_top.`name` AS 'subcategory', pro_cata.`name` AS 'productname', bp.`color` AS 'color', cata_size.`size` AS 'size', COUNT(bp.`id_backpocket`) AS cnt, bp.`size` AS id_size, cata_color.`id` AS id_color
												FROM `tl_backpocket` AS bp
												INNER JOIN `product_catalog` AS pro_cata ON pro_cata.`id`=bp.`id_catalog`
												INNER JOIN `tl_taxonomy` AS pro_category ON pro_category.`id`=pro_cata.`category`
												INNER JOIN `tl_taxonomy` AS pro_cate_top ON pro_cate_top.`id`=pro_category.`pid`
												INNER JOIN `catalog_colour` AS cata_color ON cata_color.`title`=bp.`color`
												INNER JOIN `catalog_size` AS cata_size ON cata_size.`id`=bp.`size`
												WHERE bp.`id_member`=?
												GROUP BY pro_cata.`skuid`, bp.`color`, cata_size.`size`")
										->execute($id_member, $time, $time);
		
		while ($objWishlists->next())
		{
			$arrWishlists[] = array(
				'id_catalog' => $objWishlists->id_catalog,
				'id_category' => $objWishlists->id_category,
				'id_color' => $objWishlists->id_color,
				'id_size' => $objWishlists->id_size,
				'skuid' => $objWishlists->skuid,
				'category' => $objWishlists->category,
				'subcategory' => $objWishlists->subcategory,
				'productname' => $objWishlists->productname,
				'color' => $objWishlists->color,
				'size' => $objWishlists->size,
			);
		}
		
		$arrMember['addressbook'] = $arrAddressbook;
		$arrMember['wishlists'] = $arrWishlists;
		$arrMember['orders'] = $arrOrders;
		
		return $arrMember;
	}
	
	/**
	 * Export Member list
	 * @params null
	 * @return null
	 */
	 public function exportMemberList()
	 {
		$arrMemberlists = array();
		
		$objMemberlists = $this->Database->prepare("SELECT tl_member.firstname, tl_member.lastname, tl_member.dateOfBirth, tl_member.gender, tl_member.social, tl_member.company, tl_member.street, tl_member.street_2, tl_member.postal, china_city.city_en, china_province.province_en, tl_member.country, tl_member.phone, tl_member.mobile, tl_member.email, tl_member.username, tl_member.registtime    	 
													FROM tl_member 
													LEFT JOIN china_province ON china_province.id=tl_member.state 
													LEFT JOIN china_city ON china_city.id=tl_member.city  
													WHERE 1")
										->execute(time(), time());
										
		if ($objMemberlists->numRows < 1)
			return;
		
		$this->loadLanguageFile('countries');
		
		$arrColumn[0] = array(
			'First Name', 'Last Name', 'Birthday', 'Gender', 'Social', 'Company', 'Street', 'Street_2', 'Post Code', 'City', 'State', 'Country', 'Phone', 'Mobile', 'Email', 'UserName', 'Member Since'
		);
		
		while ($objMemberlists->next())
		{
			$arrMemberlists[] = array(
				'firstname' => $objMemberlists->firstname,
				'lastname' => $objMemberlists->lastname,
				'dateOfBirth' => ($objMemberlists->dateOfBirth) ? date('m/d/Y', $objMemberlists->dateOfBirth) : date("m/d/Y"),
				'gender' => $objMemberlists->gender,
				'social' => $objMemberlists->social,
				'company' => $objMemberlists->company,
				'street' => $objMemberlists->street,
				'street_2' => $objMemberlists->street_2,
				'postal' => $objMemberlists->postal,
				'city_en' => $objMemberlists->city_en,
				'province_en' => $objMemberlists->province_en,
				'country' => $countries[$objMemberlists->country],
				'phone' => $objMemberlists->phone,
				'mobile' => $objMemberlists->mobile,
				'email' => $objMemberlists->email,
				'username' => $objMemberlists->username,
				'registtime' => date('Y-m-d', $objMemberlists->registtime),
			);
		}
		
		$arrMemberlists = array_merge($arrColumn, $arrMemberlists);
		include_once(TL_ROOT."/plugins/phpexcel/excel-xml.php");
		$objExcel = Excel_XML_Generate::getInstance();
		$objExcel->generateExcel('memberlist_' . date("Y-m-d"), $arrMemberlists);
	 }
	 
	 /**
	  * Export inventory list
	  * @params null
	  * @return null
	  */
	 public function exportInventory()
	 {
		$arrInventory = array();
		
		$objInventory = $this->Database->prepare("SELECT product_catalog.name, catalog_colour.title, catalog_size.size, tl_catalog_attribute.catalog_stock  
												FROM tl_catalog_attribute
												INNER JOIN product_catalog ON product_catalog.id=tl_catalog_attribute.id_catalog
												INNER JOIN catalog_colour ON catalog_colour.id=tl_catalog_attribute.id_color
												INNER JOIN catalog_size ON catalog_size.id=tl_catalog_attribute.id_size 	
												WHERE 1")
										->execute(time(), time());
		
		if ($objInventory->numRows < 1)
			return;
		
		$arrColumn[0] = array(
			'Product Name', 'Colour', 'Size', 'Stock'
		);
		
		while ($objInventory->next())
		{
			$arrInventory[] = array(
				'name' => $objInventory->name,
				'title' => $objInventory->title,
				'size' => $objInventory->size,
				'catalog_stock' => $objInventory->catalog_stock,
			);
		}
		
		$arrInventory = array_merge($arrColumn, $arrInventory);
		include_once(TL_ROOT."/plugins/phpexcel/excel-xml.php");
		$objExcel = Excel_XML_Generate::getInstance();
		$objExcel->generateExcel('invetory_' . date("Y-m-d"), $arrInventory);
	 }
	 
	 /**
	  * Export wishlist
	  * @params int $id_member
	  * @return null
	  */
	 public function exportWishlist($id_member=0)
	 {
		$time = time();
		$conditon = "WHERE 1";
		
		if ($id_member)
			$conditon .= " AND bp.`id_member`=" . $id_member;
			
		$objWishlists = $this->Database->prepare("SELECT pro_cata.`skuid`, pro_category.`name` AS 'category', pro_cate_top.`name` AS 'subcategory', pro_cata.`name` AS 'productname', bp.`color` AS 'color', cata_size.`size` AS 'size', COUNT(bp.`id_backpocket`) AS cnt, CONCAT(mem.`firstname`, ' ', mem.`lastname`) AS member_name, mem.`email`, pro_cata.`img` AS product_img  
												FROM `tl_backpocket` AS bp
												INNER JOIN `product_catalog` AS pro_cata ON pro_cata.`id`=bp.`id_catalog`
												INNER JOIN `tl_taxonomy` AS pro_category ON pro_category.`id`=pro_cata.`category`
												INNER JOIN `tl_taxonomy` AS pro_cate_top ON pro_cate_top.`id`=pro_category.`pid`
												INNER JOIN `catalog_size` AS cata_size ON cata_size.`id`=bp.`size`
												INNER JOIN `tl_member` AS mem ON bp.id_member=mem.id
												" . $conditon
												. " GROUP BY pro_cata.`skuid`, bp.`color`, cata_size.`size`")
										->execute($time, $time);
		
		$arrColumn[0] = array(
			'Skuid', 'Category', 'Sub-Category', 'Product Name', 'User Name', 'Email', 'Product Image', 'Color', 'Size', 'Count of added'
		);
		
		while ($objWishlists->next())
		{
			$arrWishlists[] = array(
				'skuid' => $objWishlists->skuid,
				'category' => $objWishlists->category,
				'subcategory' => $objWishlists->subcategory,
				'productname' => $objWishlists->productname,
				'member_name' => $objWishlists->member_name,
				'email' => $objWishlists->email,
				'product_img' => $objWishlists->product_img,
				'color' => $objWishlists->color,
				'size' => $objWishlists->size,
				'cnt' => $objWishlists->cnt,
			);
		}
		
		if ($arrWishlists)
		{
			$arrWishlists = array_merge($arrColumn, $arrWishlists);
			include_once(TL_ROOT."/plugins/phpexcel/excel-xml.php");
			$objExcel = Excel_XML_Generate::getInstance();
			$objExcel->generateExcel('wishlist_' . $id_member . '_' . date("Y-m-d"), $arrWishlists);
		}
		else
		{
			$this->redirect("typolight/main.php?do=memberlist&act=detail&id=" . $id_member);exit;
		}
	 }
}

?>